package com.litidea.dao.sqls;

import static com.litidea.commons.keys.DBKeys.CURRENT_TIMESTAMP;
import static com.litidea.commons.keys.DBKeys.DATE_FORMAT_DATE;
import static com.litidea.commons.keys.DBKeys.DATE_FORMAT_DATETIME;
import static com.litidea.commons.keys.DBKeys.TO_DATE_FORMAT;
import static com.litidea.dao.DAOKeys.TABLE_START;

/**
 * 保存SystemDAO的SQL语句
 * 
 * @author
 * 
 */
public class SystemDAOSqls {

	public final static String MENUS_TABLE = TABLE_START + "menus";

	public final static String DEPT_TBALE = TABLE_START + "depts";

	public final static String ROLE_TBALE = TABLE_START + "roles";

	public final static String ROLE_MENU_TABLE = TABLE_START + "role_menus";

	public final static String USER_TABLE = TABLE_START + "users";

	public final static String USER_ROLE_TABLE = TABLE_START + "user_roles";

	public final static String USER_LOGIN_TABLE = TABLE_START + "login_logs";

	// 获取菜单信息
	public final static String BASE_MENU_SQL = "select t.id,t.name,t.parent_id,t.level_id,t.all_parent_id,t.menu_desc,t.click_type,t.url,t.target,t.func_name,t.object_id,"
			+ "t.is_muti_object,t.is_refresh,t.js_files,t.location_key,t.order_id,t.busi_key,t.menu_type,expand_child,sys_name,can_del from "
			+ MENUS_TABLE + " t ";

	// 获取一级菜单列表(带权限)
	public final static String GET_PRIV_FIRST_SQL = BASE_MENU_SQL
			+ " where id in(select distinct substr(t3.all_parent_id , 2 , instr(t3.all_parent_id,'}{') - 2)"
			+ " from "
			+ TABLE_START
			+ "user_roles t1,"
			+ TABLE_START
			+ "role_menus t2,"
			+ TABLE_START
			+ "menus t3 where t1.role_id=t2.role_id and t2.menu_id=t3.id and t1.user_id=?) order by order_id";

	// 根据流程关键字标示，获取菜单基本信息
	public final static String GET_MENU_INFO_BY_WF_KEY = BASE_MENU_SQL
			+ " where WF_MENU_KEY=?";

	// 获取组织架构信息
	public final static String BASE_DEPT_SQL = "select id,t.dept_name,t.parent_id,t.all_parent_id,t.dept_full_name,t.level_id,t.order_id,"
			+ "t.create_date,"
			+ TO_DATE_FORMAT
			+ "(t.dept_create_date,'"
			+ DATE_FORMAT_DATE
			+ "') dept_create_date,t.chg_date,t.dept_responsible,t.dept_desc,t.dept_type, t.dept_mgr deptMgr, (select trm.name from litidea_users trm where trm.id=t.dept_mgr) deptMgrName from "
			+ DEPT_TBALE + " t";

	// 写入新的组织架构信息
	public final static String INSERT_DEPT_SQL = "insert into "
			+ DEPT_TBALE
			+ "(id,dept_name,parent_id,all_parent_id,dept_full_name,level_id,"
			+ "order_id,create_date,dept_create_date,chg_date,dept_responsible,dept_desc,dept_type) values(?,?,?,?,?,?,?,"
			+ CURRENT_TIMESTAMP + "," + CURRENT_TIMESTAMP + ","
			+ CURRENT_TIMESTAMP + ",?,?,?)";

	// 修改组织架构信息
	public final static String UPDATE_DEPT_SQL = "update " + DEPT_TBALE
			+ " set dept_name=?,dept_full_name=?,"
			+ "chg_date=" + CURRENT_TIMESTAMP
			+ ",dept_responsible=?,dept_desc=?,dept_type=? where id=?";
	
	// 设置部门领导
	public final static String SET_DEPT_MGR_SQL = "update " + DEPT_TBALE
			+ " set dept_mgr=? where id=?";

	// 删除组织架构信息
	public final static String DELETE_DEPT_SQL = "delete from " + DEPT_TBALE
			+ " where id=?";

	// 级联删除组织架构
	public final static String DELETE_DEPT_CASCADE = "delete from "
			+ DEPT_TBALE + " where all_parent_id like ?||'%'";

	// 将所有子部门的信息，移动到父亲部门
	public final static String MOVE_TO_PARENT = "update "
			+ DEPT_TBALE
			+ " set all_parent_id=replace(all_parent_id,?,''),"
			+ "dept_full_name=replace(dept_full_name,?,''),level_id=level_id-1,chg_date="
			+ CURRENT_TIMESTAMP + " where all_parent_id like ?";

	// 将所有直接子部门的orderID修改掉
	public final static String UPDATE_CHILD_ORDER = "update " + DEPT_TBALE
			+ " set order_id=?+1,parent_id=? where parent_id=?";

	// 将某节点后续子节点的Order_ID增加2，以放置一个新的节点
	public final static String ADD_2_AFTER_ORDER = "update " + DEPT_TBALE
			+ " set order_id=order_id+2 where parent_id=? and order_id>?";
	public final static String ADD_2_AFTER_FIRST_LEVEL = "update " + DEPT_TBALE
			+ " set order_id=order_id+2 where level_id=1 and order_id>?";

	// 将某节点后续子节点包括自己的orderID增加1，以留出一位写入新的节点
	public final static String ADD_1_AFTER_ORDER = "update " + DEPT_TBALE
			+ " set order_id=order_id+1 where parent_id=? and order_id>=?";
	public final static String ADD_1_AFTER_FIRST_LEVEL = "update " + DEPT_TBALE
			+ " set order_id=order_id+1 where level_id=1 and order_id>=?";

	// 将一个节点移动到某个指定的节点之前、之后或其子节点
	public final static String UPDATE_DEPT_INFO = "update "
			+ DEPT_TBALE
			+ " set order_id=?,all_parent_id=?,level_id=?,dept_full_name=?,parent_id=?"
			+ " where id=?";

	// 级联移动子节点信息
	public final static String MOVE_CASCADE_SQL = "update "
			+ DEPT_TBALE
			+ " set all_parent_id=replace(all_parent_id,?,?),"
			+ "dept_full_name=replace(dept_full_name,?,?),level_id=(level_id+?),chg_date="
			+ CURRENT_TIMESTAMP + " where all_parent_id like ? and id <> ?";

	// 获取组织架构的一个序列号
	public final static String GET_DEPT_SEQ_SQL = "select max(id) from "
			+ DEPT_TBALE;

	// 获取某一个父节点下子节点的最大ORDER_ID
	public final static String GET_MAX_ORDER_BY_PARENT = "select max(order_id)+1 from "
			+ DEPT_TBALE + " where parent_id=?";

	// 获取是否包含子节点
	public final static String GET_COUNTER_BY_PARENT = "select count(order_id) from "
			+ DEPT_TBALE + " where parent_id=?";

	// 获取第一层的ORDER_ID(应对第一层节点的增加)
	public final static String GET_MAX_ORDER_FIRST_LEVEL = "select max(order_id)+1 from "
			+ DEPT_TBALE + " where level_id=1";

	/**
	 * ROLE
	 */
	// 获取角色表最大的排序ID
	public final static String GET_MAX_ROLE_ORDER = "select max(order_id)+1 from "
			+ ROLE_TBALE;

	// 获取角色表最大的排序ID
	public final static String GET_MAX_ROLE_ID = "select max(id)+1 from "
			+ ROLE_TBALE;

	// 新增一个角色
	public final static String ADD_NEW_ROLE = "insert into "
			+ ROLE_TBALE
			+ "(id,name,role_responsible,create_date,chg_date,menu_nums,members,order_id,role_key)"
			+ " values(?,?,?," + CURRENT_TIMESTAMP + "," + CURRENT_TIMESTAMP
			+ ",0,0,?,?)";

	// 查询角色Base_SQL_start
	public final static String SEARCH_ROLE_BASE = "select id,name,role_responsible,"
			+ TO_DATE_FORMAT
			+ "(create_date , '"
			+ DATE_FORMAT_DATE
			+ "') create_date,"
			+ TO_DATE_FORMAT
			+ "(chg_date,'"
			+ DATE_FORMAT_DATE + "') chg_date,menu_nums,members,role_key from ";

	public final static String SEARCH_ROLE_START = SEARCH_ROLE_BASE
			+ ROLE_TBALE + " where 1=1 ";
	public final static String SEARCH_ROLE_END = " order by order_id limit ?,?";

	public final static String SEARCH_ROLES = SEARCH_ROLE_BASE
			+ ROLE_TBALE
			+ " where (select FIND_IN_SET(id, col1) from (select ? as col1) as tab1)!=0";

	// 查询角色的count信息
	public final static String SEARCH_ROLE_COUNT = "select count(1) from "
			+ ROLE_TBALE + " where 1=1";

	// 修改角色信息
	public final static String EDIT_ROLE = "update " + ROLE_TBALE
			+ " set name=?,role_responsible=?,chg_date=" + CURRENT_TIMESTAMP
			+ ",role_key=? where id=?";

	// 删除角色信息
	public final static String DEL_ROLE = "delete from " + ROLE_TBALE
			+ " where id=?";

	// 检测角色权限的数量
	public final static String GET_ROLE_MENUS = "select count(1) from "
			+ ROLE_MENU_TABLE + " where role_id=?";

	// 获取role对应的菜单列表
	public final static String ROLE_MENUS = "select t2.all_parent_id from "
			+ ROLE_MENU_TABLE + " t1," + TABLE_START
			+ "menus t2 where t1.menu_id=t2.id and role_id=?";

	// 删除Role-menu对应关系
	public final static String DEL_ROLE_MENUS = "delete from "
			+ ROLE_MENU_TABLE + " where role_id=?";

	// 新增一个role_menu关系
	public final static String ADD_ROLE_MENUS = "insert into "
			+ ROLE_MENU_TABLE + "(role_id,menu_id) values(?,?)";

	// 修改角色对应的菜单数量
	public final static String UPDATE_ROLE_MENUS = "update " + ROLE_TBALE
			+ " set menu_nums=? where id=?";

	/**
	 * 用户
	 */
	// 获取角色表最大的排序ID
	public final static String GET_MAX_USER_ORDER = "select max(order_id)+1 from "
			+ USER_TABLE;

	// 查询用户Base_SQL_start
	public final static String SEARCH_USER_BASE = "select a.id,a.name,id_card,login_name,email,tel,tel2,mobile,user_content,certificate,user_desc,gender,"
			+ TO_DATE_FORMAT
			+ "(birthday,'"
			+ DATE_FORMAT_DATE
			+ "') birthday,dept_id,"
			+ TO_DATE_FORMAT
			+ "(before_login,'"
			+ DATE_FORMAT_DATETIME
			+ "') before_login,"
			+ TO_DATE_FORMAT
			+ "(last_login,'"
			+ DATE_FORMAT_DATETIME
			+ "') last_login,"
			+ "before_login_ip,last_login_ip ";

	// 查询用户
	public final static String SEARCH_USER_START = SEARCH_USER_BASE
			+ ",b.dept_name deptName from (select * from " + USER_TABLE
			+ " where 1=1 ";

	public final static String SEARCH_USER_END = " order by order_id limit ?,?) a , "
			+ DEPT_TBALE + " b" + " where a.dept_id = b.id";

	// 根据用户名查找用户
	public final static String SEARCH_USER_BY_LOGIN = SEARCH_USER_BASE
			+ ",login_pass,b.dept_name deptName  from " + USER_TABLE + " a,"
			+ DEPT_TBALE + " b where login_name=? and a.dept_id = b.id";

	// 根据ID查找用户
	public final static String SEARCH_USER_BY_ID = SEARCH_USER_BASE
			+ ",login_pass,b.dept_name deptName from " + USER_TABLE + " a,"
			+ DEPT_TBALE + " b where a.id=? and a.dept_id = b.id";

	// 根据roleId查找用户
	public final static String SEARCH_USER_BY_ROLES = SEARCH_USER_BASE
			+ ",login_pass,c.dept_name deptName from "
			+ USER_TABLE
			+ "b,"
			+ DEPT_TBALE
			+ " c ,"
			+ USER_ROLE_TABLE
			+ "d where b.dept_id = c.id "
			+ "and b.id = d.user_id and find_in_set(d.role_id,?)<>0";

	// 获取用户数据量
	public final static String SEARCH_USER_COUNT = "select count(1) from "
			+ USER_TABLE + " where 1=1";

	// 获取一个新的用户ID
	public final static String GET_USER_SEQ_SQL = "select max(id)+1 from "
			+ USER_TABLE;

	// 添加一个用户
	public final static String ADD_USER = "insert into " + USER_TABLE
			+ " (id,name,id_card,login_name,login_pass,email,tel,"
			+ "tel2,mobile,user_content,certificate,user_desc,gender,"
			+ "birthday,dept_id,order_id)"
			+ " values(?,?,?,?,?,?,?,?,?,?,?,?,?," + TO_DATE_FORMAT + "(?,'"
			+ DATE_FORMAT_DATE + "'),?,?)";
	// 修改一个用户
	public final static String CHG_USER = "update "
			+ USER_TABLE
			+ " set name=?,id_card=?,login_name=?,email=?,tel=?,tel2=?,mobile=?,"
			+ "user_content=?,certificate=?,user_desc=?,gender=?,birthday="
			+ TO_DATE_FORMAT + "(?,'" + DATE_FORMAT_DATE + "'),"
			+ "dept_id=? where id=?";

	// 修改用户的密码
	public final static String CHG_USER_PASS = "update " + USER_TABLE
			+ " set login_pass=? where id=?";

	// 删除一个用户
	public final static String DEL_USER = "delete from " + USER_TABLE
			+ " where id=?";

	// 查看用户的角色
	public final static String SEARCH_ROLES_BY_USER = SEARCH_ROLE_BASE
			+ ROLE_TBALE + " where id in(select role_id from "
			+ USER_ROLE_TABLE + " where user_id=?)";

	// 查看不是一个用户的角色信息
	public final static String SEARCH_ROLES_NOT_USER = SEARCH_ROLE_BASE
			+ ROLE_TBALE + " where id not in(select role_id from "
			+ USER_ROLE_TABLE + " where user_id=?)";

	// 删除一个用户角色信息
	public final static String DEL_USER_ROLES = "delete from "
			+ USER_ROLE_TABLE + " where user_id=?";

	// 添加一个角色用户关联关系
	public final static String ADD_USER_ROLE = "insert into " + USER_ROLE_TABLE
			+ "(user_id,role_id) values(?,?)";

	// 修改角色对应的人员数量(这个SQL待定)
	public final static String CHG_ROLE_USERCOUNT = "update " + ROLE_TBALE
			+ " t1 set members=" + "(select count(1) from " + USER_ROLE_TABLE
			+ " t2 where t1.id=t2.role_id) "
			+ "where find_in_set(id,?)<>0";

	// 修改密码(乐观加锁，若更新行数为0行，则修改失败，不需要先校验，再更新)
	public final static String CHG_PASS = "update " + USER_TABLE
			+ " set login_pass=? where id=? and login_pass=?";

	// 获取用户是否存在
	public final static String GET_USER_EXISTS = "select count(1) from "
			+ USER_TABLE + " where login_name=?";

	// 获取角色表最大的排序ID
	public final static String GET_USER_LOGIN_ID = "select seq_"
			+ USER_LOGIN_TABLE + "_id.nextval from dual";

	// 写入一个登陆日志
	public final static String ADD_USER_LOGIN = "insert into "
			+ USER_LOGIN_TABLE + "(user_id,login_time) values(?,"
			+ CURRENT_TIMESTAMP + ")";

	// 登出用户，写入登出日志
	public final static String USER_LOGIN_OUT = "update " + USER_LOGIN_TABLE
			+ " set LOGIN_OUT_TIME=" + CURRENT_TIMESTAMP
			+ ",AUTO_OUT=? where id=?";

	// 登陆时写入用户登录信息
	public final static String UPDATE_USER_LOGIN_INFO = "update " + USER_TABLE
			+ " set before_login=last_login,last_login=" + CURRENT_TIMESTAMP
			+ ",before_login_ip=last_login_ip,last_login_ip=? where id=?";
}
